package com.archetypus.projectschedule.common.data.projects;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.archetypus.projectschedule.common.data.MySqlDAOFactory;
import com.archetypus.projectschedule.common.dto.projects.Project;
import com.archetypus.projectschedule.common.dto.projects.ProjectKey;
import com.archetypus.projectschedule.common.exceptions.ProjectSchedulerException;

public class MySqlProjectDAO implements ProjectDAO {

	@Override
	public void delete(Project project) throws ProjectSchedulerException {
		String sql = "DELETE FROM projects WHERE id=?";
		Connection conn = null;
		PreparedStatement stmt = null;
		
		try{
			conn = MySqlDAOFactory.createConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, project.getProjectKey().getId());
			stmt.executeUpdate();
		}catch(Exception ex){
			ex.printStackTrace();
			throw new ProjectSchedulerException("Could not delete register");
		}finally{
			try{
				stmt.close();
				conn.close();
			}catch(Exception e){}
		}
	}

	@Override
	public Project find(Project project) throws ProjectSchedulerException {
		String sql = "SELECT id, name, description FROM projects WHERE id=?";
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		Project retrievedProject = null;
		
		try{
			conn = MySqlDAOFactory.createConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, project.getProjectKey().getId());
			rs = stmt.executeQuery();
			rs.next();
			retrievedProject = new Project();
			retrievedProject.setDescription(rs.getString("description"));
			retrievedProject.setName(rs.getString("name"));
			retrievedProject.setProjectKey(new ProjectKey(rs.getInt("id")));
		}catch(Exception ex){
			ex.printStackTrace();
			throw new ProjectSchedulerException("Could not retrieve register");
		}finally{
			try{
				stmt.close();
				conn.close();
			}catch(Exception e){}
		}
		return retrievedProject;
	}

	@Override
	public Project insert(Project project) throws ProjectSchedulerException {
		String sql = "INSERT INTO projects(name,description) VALUES(?,?)";
		Connection conn = null;
		PreparedStatement stmt = null;
		
		try{
			conn = MySqlDAOFactory.createConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, project.getName());
			stmt.setString(2, project.getDescription());
			stmt.executeUpdate();
		}catch(Exception ex){
			ex.printStackTrace();
			throw new ProjectSchedulerException("Could not insert register",ex);
		}finally{
			try{
				stmt.close();
				conn.close();
			}catch(Exception e){}
		}
		return project;
	}

	@Override
	public List<Project> list() throws ProjectSchedulerException {
		String sql = "SELECT id, name, description FROM projects";
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		List<Project> projectsList = new ArrayList<Project>();
		
		try{
			conn = MySqlDAOFactory.createConnection();
			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery();
			while(rs.next()){
				Project p = new Project();
				p.setDescription(rs.getString("description"));
				p.setName(rs.getString("name"));
				p.setProjectKey(new ProjectKey(rs.getInt("id")));
				projectsList.add(p);
			}
		}catch(Exception ex){
			ex.printStackTrace();
			throw new ProjectSchedulerException("Could not retrieve register");
		}finally{
			try{
				stmt.close();
				conn.close();
			}catch(Exception e){}
		}
		return projectsList;
	}

	@Override
	public void update(Project project) throws ProjectSchedulerException {
		String sql = "UPDATE projects SET name=?, description=? WHERE id=?";
		Connection conn = null;
		PreparedStatement stmt = null;
		try{
			conn = MySqlDAOFactory.createConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, project.getName());
			stmt.setString(2, project.getDescription());
			stmt.setInt(3, project.getProjectKey().getId());
			stmt.executeUpdate();
		}catch(Exception ex){
			ex.printStackTrace();
			throw new ProjectSchedulerException("Could not update register");
		}finally{
			try{
				stmt.close();
				conn.close();
			}catch(Exception e){}
		}
	}

}
